using AutoMapper;
using Microsoft.AspNetCore.Hosting;
using Microsoft.AspNetCore.Http;
using SqlSugar;
using SX.BaseService.Repository.Core;
using SX.BaseService.Repository.Entity;
using SX.BaseService.Service.Events;
using SX.BaseService.Service.Implement.SysUser.ViewModel;
using SX.BaseService.Service.ViewModel;
using SX.Core;
using SX.Core.Auth;
using SX.Core.EventBus.Abstractions;
using SX.Core.EventBus.Db;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace SX.BaseService.Service
{
    /// <summary>
	/// 账户服务层
	/// </summary>
    public class SysUserService:DbContext,ISysUserService
    {
        private readonly IMapper _mapper;
        private IEventBus _eventBus;
        private readonly IEventLogService _eventLogService;
        private readonly ILoginInfo _loginInfo;
        private readonly IWebHostEnvironment _webHostEnvironment;

        public SysUserService(IMapper mapper,IEventBus eventBus,IEventLogService eventLogService,ILoginInfo loginInfo,IWebHostEnvironment webHostEnvironment)
        {
            _eventLogService = eventLogService;
            _eventBus = eventBus;
            _mapper = mapper;
            _loginInfo = loginInfo;
            _webHostEnvironment = webHostEnvironment;
        }

        /// <summary>
        /// 新增
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public async Task<IApiResult> Add(UserAddModel model)
        {
            var user = _mapper.Map<SysUserEntity>(model);
            user.Id = GuidHelper.NewSequentialGuid();
            user.CreateTime = DateTime.Now;
            user.Password = Md5Encrypt.Encrypt32(model.PassWord);
            var res = await Db.Insertable<SysUserEntity>(user).ExecuteCommandAsync();
            if (res > 0)
            {
                if (model.Roles.Any())
                {
                    var roles = model.Roles.Select(a => new SysUserRoleEntity { SysUserId = user.Id, SysRoleId = a });
                    if (roles.Any())
                        await Db.Insertable<SysUserRoleEntity>(roles.ToList()).ExecuteCommandAsync();
                }

                //添加组织关系
                if (model.OrganizeId.NotNull())
                {
                    SysOrganizeUserEntity data = new SysOrganizeUserEntity { SysUserId = user.Id, SysOrganizeId = model.OrganizeId };
                    //批量插入数据到[SysOrganizeUser]
                    await Db.Insertable(data).ExecuteCommandAsync();
                }

                //发送mq事件消息
                UserAddIntegrationEvent @event = new UserAddIntegrationEvent
                {
                    BusinessId = user.Id.To_String(),
                    Id = GuidHelper.NewSequentialGuid().To_String(),
                    UserName = model.UserName
                };
                //添加消息记录到数据库
                await _eventLogService.AddEventTodbAsync(@event);
                //发送消息
                _eventBus.Publish(@event);
            }
            return ApiResult.Result(res > 0);
        }
        /// <summary>
        /// 删除
        /// </summary>
        /// <param name="ids"></param>
        /// <returns></returns>
        public async Task<IApiResult> Delete(List<string> ids)
        {
            var res=await Db.Deleteable<SysUserEntity>().In(ids).ExecuteCommandAsync();
            return ApiResult.Result(res > 0);
        }

        /// <summary>
        /// 查询单个
        /// </summary>
        /// <param name="userId"></param>
        /// <returns></returns>
        public async Task<IApiResult> Get(string userId)
        {
            var entity = await Db.Queryable<SysUserEntity>().InSingleAsync(userId);
            if (entity == null)
                return ApiResult.NotOk("账户不存在");

            var model = _mapper.Map<UserUpdateModel>(entity);
            var all = await Db.Queryable<SysOrganizeUserEntity>().ToListAsync();
            model.OrganizeId = all.Where(a => a.SysUserId == userId).Select(b => b.SysOrganizeId).FirstOrDefault();
            var roles = Db.Ado.SqlQuery<SysRoleEntity>(@"select r.id,r.Name,r.Remarks,r.CreateTime, 1 as Selected from sysrole r left join SysUserRole ur on r.Id=ur.SysRoleId where SysUserId=@UId 
              union all
               select *, 0 as Selected from SysRole", new { UId = userId });
            model.Roles = roles.Where(a => a.Selected == true).Select(t => t.Id).Distinct().ToList(); ;
            var org = await Db.Queryable<SysOrganizeEntity>().InSingleAsync(model.OrganizeId);
            model.OrganizeName = org?.Name;
            return ApiResult.Ok(model);
        }

        /// <summary>
        /// 查询
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public async Task<IApiResult> Query(UserQueryModel model)
        {
            //这里不能var，会获取不到总数量
            RefAsync<int> total = 0;
            string sql = @"select distinct s.id,s.UserName,s.TrueName,s.Phone,s.Status,s.CreateTime,s.Sex,s.Adress,s.HeadPic,s.UpLoginDate,s.Remarks,(select top 1 name from SysOrganize where id=sou.SysOrganizeId)orgName from SysUser s left join SysOrganizeUser sou on s.Id=sou.SysUserId left join SysUserRole sur on sur.SysUserId=s.Id ";
            var queryable = Db.SqlQueryable<SysUserEntity>(sql);
            var source = await queryable
                .OrderBy(x => x.CreateTime, OrderByType.Desc)
                .WhereIF(model.UserName.NotNull(), x => x.UserName.Contains(model.UserName))
                .ToPageListAsync(model.PageIndex, model.PageSize, total);
            PageResult<SysUserEntity> result = new PageResult<SysUserEntity>()
            {
                Rows = source,
                TotalCount = total
            };
            return ApiResult.Ok(result);
        }
        /// <summary>
        /// 查询账户所所有的权限菜单
        /// </summary>
        /// <param name="userId"></param>
        /// <returns></returns>
        public async Task<List<string>> QueryPermissionList(string userId)
        {
            var list_permission_code =await Db.Queryable<SysPermissionEntity>()
              .InnerJoin<SysRolePermissionEntity>((a, b) =>a.Id==b.PermissionId)
              .InnerJoin<SysUserRoleEntity>((a, b, c) => c.SysRoleId == b.RoleId)
              .Where((a,b,c) =>c.SysUserId==userId)
              .Select<string>((a) =>a.Code)
              .ToListAsync();
            return list_permission_code;
        }
        /// <summary>
        /// 更新
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public async Task<IApiResult> Update(UserUpdateModel model)
        {
            var entity= _mapper.Map<SysUserEntity>(model);
            var res = await Db.Updateable(entity).ExecuteCommandAsync();

            if (res>0)
            {
                //更新角色
                if (model.Roles.Any())
                {
                    //删除当前用户所有的角色信息
                    var count = await Db.Deleteable<SysUserRoleEntity>().Where(x => x.SysUserId == entity.Id).ExecuteCommandAsync();
                    var roles = new List<SysUserRoleEntity>();
                    foreach (var item in model.Roles)
                    {
                        roles.Add(new SysUserRoleEntity { SysUserId = entity.Id, SysRoleId = item });
                    }

                    await Db.Insertable(roles).ExecuteCommandAsync();
                }
                //添加组织关系
                if (model.OrganizeId.NotNull())
                {
                    //删除SysOrganizeUser表中该用户的记录
                    await Db.Deleteable<SysOrganizeUserEntity>().Where(x => x.SysUserId == entity.Id).ExecuteCommandAsync();
                    SysOrganizeUserEntity data = new SysOrganizeUserEntity { SysUserId = entity.Id, SysOrganizeId = model.OrganizeId };
                    //批量插入数据到[SysOrganizeUser]
                    await Db.Insertable(data).ExecuteCommandAsync();

                }
                return ApiResult.Ok("修改成功");
            }
            else
            {
                return ApiResult.NotOk("修改失败");
            }

        }

        public async Task<IApiResult> EditPwd(string oldpwd, string newpwd)
        {
            //先判断oldpwd是否正确
            var user = await Db.Queryable<SysUserEntity>().InSingleAsync(_loginInfo.UserId);
            if (!user.Password.Equals(Md5Encrypt.Encrypt32(oldpwd), StringComparison.CurrentCultureIgnoreCase))
            {
                return ApiResult.NotOk("原始密码不匹配，修改失败");
            }
            user.Password = Md5Encrypt.Encrypt32(newpwd);
            var res = await Db.Updateable(user).ExecuteCommandAsync();
            if (res<=0)
                return ApiResult.NotOk("修改失败，请刷新页面");
            return ApiResult.Ok();

        }

        public async Task<IApiResult> UpdateBasic(UpdateModel_Basic model)
        {
            var entity = await Db.Queryable<SysUserEntity>().InSingleAsync(_loginInfo.UserId);

            var uploadCof = new UploadConfig(_webHostEnvironment.WebRootPath);
            //保存文件
            var result = await FileUploadHelper.UploadAsync(model.File, uploadCof, "Avatar");
            if (result.Success)
            {
                entity.HeadPic = result.Data;
            }
            if (model.TrueName.NotNull())
                entity.TrueName = model.TrueName;
            if (model.Remarks.NotNull())
                entity.Remarks = model.Remarks;
            var res = await Db.Updateable(entity).ExecuteCommandAsync();
            if (res<=0)
            {
                return ApiResult.NotOk("更新基本信息失败！");
            }
            return ApiResult.Ok();
        }

        public async Task<DataTable> QueryUserDTable()
        {
            List<UserExcelModel> list = await Db.SqlQueryable<UserExcelModel>(@"select UserName,sr.Name RoleName,TrueName,(case Sex when 0 then N'男' else N'女' end)Sex,Phone,Email, Adress from SysUser su left join SysUserRole sur on sur.SysUserId=su.Id left join SysRole sr on sr.Id=sur.SysRoleId").ToListAsync();
            if (list.Count <= 0)
                return null;
            //把list转为datatable
            var table = list.ToDataTable();
            return table;
        }

        public async Task<IApiResult> ImportData(IFormFile file)
        {
            if (file.Length <= 0)
                return ApiResult.NotOk("请先选择导入文件");

            var ext = Path.GetExtension(file.FileName).ToLower();
            if (ext != ".xls" && ext != ".xlsx")
            {
                return ApiResult.NotOk("文件必须为.xlsx|.xls");
            }

            MemoryStream ms = new MemoryStream();
            file.CopyTo(ms);
            ms.Seek(0, SeekOrigin.Begin);
            //加载
            ExcelHelper.Load(file.FileName, ms);
            ms.Flush();
            ms.Dispose();
            var count = ExcelHelper.GetSheetsCount();
            var num = 0;
            for (int i = 0; i < count; i++)
            {
                string sheetname = ExcelHelper.GetSheetName(i);
                System.Data.DataTable table = ExcelHelper.ExcelToDataTable(sheetname, true);
                if (table == null || table.Rows.Count == 0)
                {
                    continue;
                }
                //插入到数据库
                num += await AddDTable(table);
            }
            if (num <= 0)
            {
                return ApiResult.NotOk("导入数据失败,请检查模板");
            }
            return ApiResult.Ok<string>(null, "成功导入" + num + "条数据");
        }

        private async Task<int> AddDTable(DataTable table)
        {
            var result = 0;
            var pwd = "123456";
            List<SysUserEntity> list_add = new List<SysUserEntity>();
            List<SysUserEntity> list_update = new List<SysUserEntity>();
            foreach (DataRow row in table.Rows)
            {
                var data = new SysUserEntity();
                data.UserName = row["账号"].To_String();
                data.TrueName = row["真实姓名"].To_String();
                data.Phone = row["电话"].To_String();
                data.Email = row["邮箱"].To_String();
                data.Adress = row["地址"].To_String();
                data.Sex = row["性别"].To_String().Trim() == "男" ? 0 : 1;
                var isExists = await Db.Queryable<SysUserEntity>().AnyAsync(x => x.UserName == data.UserName);
                if (!isExists)
                {
                    // 插入数据
                    data.Id = GuidHelper.NewSequentialGuid(SequentialGuidType.SequentialAtEnd);
                    data.Password = Md5Encrypt.Encrypt32(pwd);
                    data.CreateTime = DateTime.Now;
                    data.Status = 1;
                    data.Deleted = 0;
                    list_add.Add(data);
                }
                else
                {
                    // 更新数据
                    list_update.Add(data);
                }
            }
            if (list_add.Count > 0)
            {
                result +=await Db.Insertable(list_add).ExecuteCommandAsync();
            }
            if (list_update.Count > 0)
            {
                result += await Db.Updateable(list_update).UpdateColumns(x=>new { x.TrueName,x.Phone,x.Email,x.Adress,x.Sex }).WhereColumns(it => new { it.UserName }).ExecuteCommandAsync();
            }

            return result;
        }
    }
}